package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Gui.Terminal;
import Model.Customer;
import Model.Invoice;
import Model.Partorder;
import Model.Product;
import Model.Salesorder;

public class DBPartorder {
	
	private final String amount ="amount";
	private final String productID ="PprodID";
	private final String salesorderID ="SalesID";
	
	private final String selectAll = String.format("SELECT %s, %s, %s FROM Partorder", amount, productID, salesorderID);
	
	private Connection con;
	
	public DBPartorder()
	{
		con = DBConnection.getInstance().getDBcon();
	}
	
	public Partorder getPartorder(String productID)
	{
		Partorder result = null;
		
		//Create statement
		Statement stmt = null;
		
		try {
			stmt = con.createStatement();
		} catch (SQLException e) { 
			Terminal.say("An error occured during statement creation", e);
		}
		
		//Execute statement
		try {
			stmt.execute(selectWhere(this.productID + " = " + productID));
		} catch (SQLException e) {
			Terminal.say("An error occured during statement execution", e);
		}
		
		//Retrieve result 
		ResultSet resSet = null;
		try {
			resSet = stmt.getResultSet();
		} catch (SQLException e) {
			Terminal.say("An error occured during statement retrieving", e);
		}
		
		try {
			if(resSet.next())
			{
				result = buildPartorder(resSet);
			}
			else {
				Terminal.say("No result was found");
			}
		} catch (SQLException e1) {
			Terminal.say("No result was found", e1);
		}
		
		//Close statement
		if(stmt != null){
			try {
				stmt.close();
			} catch (SQLException e) {
				Terminal.say("An error occured during closing the statement", e);
			}
		}
		
		return result;
	}
	
	public String selectWhere(String condition)
	{
		return selectAll + " WHERE " + condition;
	}
	
	public ArrayList<Partorder> getAllPartorders(String salesorderID)
	{
		//Create statement
		Statement stmt = null;
		try {
			stmt = con.createStatement();
		} catch (SQLException e) {
			Terminal.say("An error occured during statement creation", e);
		}
		
		//Execute statement
		try {
			stmt.execute(selectWhere(this.salesorderID + " = " + salesorderID));
		} catch (SQLException e) {
			Terminal.say("An error occured during statement execution", e);
		}
		
		
		//Retrieve statement
		ResultSet resSet = null;
		try {
			resSet = stmt.getResultSet();
		} catch (SQLException e) {
			Terminal.say("An error occured during statement retrieving", e);
		}
		
		ArrayList<Partorder> result = getResult(resSet);
		
		//Close statement
		try {
			stmt.close();
		} catch (SQLException e) {
			Terminal.say("An error occured during statement closure", e);
		}
		
		return result;
	}
	
	public ArrayList<Partorder> getResult(ResultSet result)
	{
		ArrayList<Partorder> partorders = new ArrayList<Partorder>();
		try {
			while(result.next())
			{
				partorders.add(buildPartorder(result));
			}
		} catch (SQLException e) {
			Terminal.say("An error occured while data was put in the ArrayList", e);
		}
		
		return partorders;
	}
	
	public boolean addPartorder(Partorder partorder)
	{
		boolean succes = true;
		
		//Create statement
			Statement stmt = null;
			try {
				stmt = con.createStatement();
			} catch (SQLException e) {
				Terminal.say("An error occured during statement creation", e);
			}
			
			//Execute statement
			int amount = partorder.getAmount();
			String productID = partorder.getProduct().getProductID();		
			String salesorderID = partorder.getSalesorderID();
			try {
				stmt.execute(String.format("INSERT INTO Partorder VALUES %s, %s, %s", amount, productID, salesorderID));
			} catch (SQLException e) {
				Terminal.say("An error occured while inserting a Salesorder object", e);
				succes = false;
			}
			
			//Close statement
			try {
				stmt.close();
			} catch (SQLException e) {
				Terminal.say("An error uccured during statement closing", e);
			}
			
			return succes;
	}
		
	public boolean deleteSalesorder(String id)
	{
		boolean succes = true;
		
		//Create statement
		Statement stmt = null;
		try {
			stmt = con.createStatement();
		} catch (SQLException e) {
			Terminal.say("An error occured during statement creation", e);
		}
		
		//Execute statement
		try {
			stmt.execute(String.format("DELETE FROM Partorder WHERE PartorderID = %s", this.salesorderID));
		} catch (SQLException e) {
			Terminal.say("An error occured during removing a Partorder object from the database", e);
			succes = false;
		}
		
		//Close statement 
		try {
			stmt.close();
		} catch (SQLException e) {
			Terminal.say("An error occured during statment closing",e);
		}
		
		return succes;
	}	
	
	
	
	private Partorder buildPartorder(ResultSet resSet)
	{		
		try {
			if(resSet.next())
			{
				int amount;
				amount = resSet.getInt(this.amount);
				DBProduct dbp = new DBProduct();
				Product productID = dbp.getProduct(resSet.getString(this.productID));
				String salesorderID = resSet.getString(this.salesorderID);
				return new Partorder(amount, productID, salesorderID);
			}
		} catch (SQLException e) {
			Terminal.say("An error occured while building a Partorder object",e);
		}
		return null;
	}

}
